﻿--truncate table  FSoftNAV2WEB.[dbo].[Object]
--truncate table  FSoftNAV2WEB.[dbo].[Object_properties]


SELECT     (REPLACE('HO$',''))
select * from [dbo].[Object] where [Database_id] = 1069298919

INSERT INTO [Object]
           ([Name]
           ,[Type]
           ,[Active]
           ,[Database_id])   
select  REPLACE(o.name,'','') , o.type, 1 ,o.object_id from sys.objects  o where (type ='U' OR type = 'V')
--AND o.name = 'Sales Price Header'

--store
INSERT INTO [Object]
           ([Name]
           ,[Type]
           ,[Active]
           ,[Database_id])   
select  REPLACE(o.name,'HO$','') , o.type, 1 ,o.object_id from sys.objects  o where (type ='P' )



INSERT INTO [dbo].[Object_properties]
           ([Name]
           ,Caption
           ,[Type]
           ,[Data_length]
           ,[Active]
           ,[Database_id]
           ,[Object_id])
select p.name,p.name,tp.name,p.max_length, 1,p.parameter_id,o.ID from sys.parameters p
inner join [dbo].[Object] o on p.object_id = o.Database_id
inner join sys.types tp on p.system_type_id = tp.system_type_id
where (o.Type = 'P'  and tp.name <> 'sysname' )
--and o.ID = 329312483
order by o.Database_id,p.parameter_id

------------
INSERT INTO [dbo].[Object_properties]
           ([Name]
           ,Caption
           ,[Type]
           ,[Data_length]
           ,[Active]
           ,[Database_id]
           ,[Object_id])

select c.name,c.name,tp.name,c.max_length, 1,c.column_id,o.ID from sys.columns c
inner join [dbo].[Object] o on c.object_id = o.Database_id
inner join sys.types tp on c.system_type_id = tp.system_type_id
where (o.Type = 'U'  and tp.name <> 'sysname' and c.name <> 'timestamp')
--AND  o.name = 'Object_properties'
--and o.ID = 1127
order by o.Database_id,c.column_id




UPDATE [dbo].[Object_properties]
   SET 
      [Show_in_form] = 1
      ,[Show_in_grid] = 1
      ,[Show_in_report] =1
      ,[Show_in_excel] = 1
      ,[Form_tab_index] = 1
      ,[Grid_order] = 1
    
      ,[Editable] = 1
     
      ,[Not_blank] = 0
    where  [Not_blank] is null
 
GO


select * from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.types tp on c.system_type_id = tp.system_type_id
where t.type = 'U'

--truncate table dbo.Object_properties
--truncate table dbo.Object
--delete dbo.Object
--truncate table RoleOfObject


-- delete FSoftNAV2WEB.[dbo].[Object]  where Type = 'V'
-- delete FSoftNAV2WEB.dbo.Object_properties  where Object_id not in (select ID from FSoftNAV2WEB.[dbo].[Object] )
Select * from 
 

--select t.name as TableName, c.name as ColumnName, t.object_id as ObjectID, t.type, t. from sys.columns c
--inner join sys.tables t on c.object_id = t.object_id
--Where t.name like '%HO%' and t.OBJECT_ID = '1165299261'
--order by t.name

/*
	update
	--truncate table  FSoftNAV2WEB.[dbo].[Object]
	--truncate table  FSoftNAV2WEB.[dbo].[Object_properties]


	SELECT     (REPLACE('HO$',''))
	select * from FSoftNAV2WEB.[dbo].[Object] where [Database_id] = 1069298919

	INSERT INTO FSoftNAV2WEB.[dbo].[Object]
			   ([Name]
			   ,[Type]
			   ,[Active]
			   ,[Database_id])   
	select  REPLACE(o.name,'HO$','') , o.type, 1 ,o.object_id from sys.objects  o where (type ='U' OR type = 'V')
	AND o.name like 'HO$Sales Price Line'


	INSERT INTO FSoftNAV2WEB.[dbo].[Object_properties]
			   ([Name]
			   ,[Type]
			   ,[Data_length]
			   ,[Active]
			   ,[Database_id]
			   ,[Object_id])

	select c.name,tp.name,c.max_length, 1,c.column_id,o.ID from sys.columns c
	inner join FSoftNAV2WEB.[dbo].[Object] o on c.object_id = o.Database_id
	inner join sys.types tp on c.system_type_id = tp.system_type_id
	where (o.Type = 'U'  and tp.name <> 'sysname' and c.name <> 'timestamp')
	AND  o.name = 'Sales Price Line'
	order by o.Database_id,c.column_id



	UPDATE FSoftNAV2WEB.[dbo].[Object_properties]
	   SET 
		  [Show_in_form] = 1
		  ,[Show_in_grid] = 1
		  ,[Show_in_report] =1
		  ,[Show_in_excel] = 1
		  ,[Form_tab_index] = 1
		  ,[Grid_order] = 1
	    
		  ,[Editable] = 1
	     
		  ,[Not_blank] = 0
		where  [Not_blank] is null
	 
	GO


	select * from sys.columns c
	inner join sys.tables t on c.object_id = t.object_id
	inner join sys.types tp on c.system_type_id = tp.system_type_id
	where t.type = 'U'

	--truncate table dbo.Object_properties
	--truncate table dbo.Object
	--delete dbo.Object
	--truncate table RoleOfObject


	-- delete FSoftNAV2WEB.[dbo].[Object]  where Type = 'V'
	-- delete FSoftNAV2WEB.dbo.Object_properties  where Object_id not in (select ID from FSoftNAV2WEB.[dbo].[Object] )
	Select * from 
	 

	--select t.name as TableName, c.name as ColumnName, t.object_id as ObjectID, t.type, t. from sys.columns c
	--inner join sys.tables t on c.object_id = t.object_id
	--Where t.name like '%HO%' and t.OBJECT_ID = '1165299261'
	--order by t.name

	
SELECT TOP 1000 [ID]
      ,[Name]
      ,[Type]
      ,[Active]
      ,[Database_id]
  FROM [FSoftNAV2WEB].[dbo].[Object]
  where Name like '%Salesperson%'
  
  SELECT *
  FROM [FSoftNAV2WEB].[dbo].Object_properties
  where-- Name like '%Salesperson%'
  Object_id = '730'
  
  
  delete [FSoftNAV2WEB].[dbo].Object_properties where Object_id = '730'
  delete [FSoftNAV2WEB].[dbo].Object where ID = '730'
  
  INSERT INTO FSoftNAV2WEB.[dbo].[Object]
			   ([Name]
			   ,[Type]
			   ,[Active]
			   ,[Database_id])   
  select  REPLACE(o.name,'HO$','') , o.type, 1 ,o.object_id from sys.objects  o where (type ='U' OR type = 'V')
	AND o.name like 'HO$Salesperson_Purchaser'
  
  INSERT INTO FSoftNAV2WEB.[dbo].[Object_properties]
			   ([Name]
			   ,[Type]
			   ,[Data_length]
			   ,[Active]
			   ,[Database_id]
			   ,[Object_id])
  select c.name,tp.name,c.max_length, 1,c.column_id,o.ID from sys.columns c
	inner join FSoftNAV2WEB.[dbo].[Object] o on c.object_id = o.Database_id
	inner join sys.types tp on c.system_type_id = tp.system_type_id
	where (o.Type = 'U'  and tp.name <> 'sysname' and c.name <> 'timestamp')
	AND  o.name = 'Salesperson_Purchaser'
	order by o.Database_id,c.column_id
	
	UPDATE FSoftNAV2WEB.[dbo].[Object_properties]
	   SET 
		  [Show_in_form] = 1
		  ,[Show_in_grid] = 1
		  ,[Show_in_report] =1
		  ,[Show_in_excel] = 1
		  ,[Form_tab_index] = 1
		  ,[Grid_order] = 1
	      ,Show_in_list =1
		  ,[Editable] = 1	     
		  ,[Not_blank] = 0
		where  [Not_blank] is null
		
		update FSoftNAV2WEB.[dbo].[Object_properties] set Caption = Name where Object_id = '1161'
				
	
SELECT TOP 1000 [ID]
      ,[Name]
      ,[Type]
      ,[Property_name]
      ,[Property_type]
      ,[Tab_name]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Report_type]
      ,[Excel_type]
      ,[Property_id]
      ,[Data_length]
      ,[Object_id]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Column_database_id]
      ,[Object_database_id]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Caption]
      ,[Option_text]
  FROM [FSoftNAV2WEB].[dbo].[Object_info]
  where Name like '%Promotion Line%'
  
  insert into [FSoftNAV2WEB].[dbo].Object_properties ([Caption]
      ,[Type]
      ,[Data_length]
      ,[Database_id]
      ,[Object_id]
      ,[Tab_name]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Report_type]
      ,[Excel_type]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Option_text]
      ,[Active])  Select [Caption]
      ,[Type]
      ,[Data_length]
      ,[Database_id]
      ,[Object_id]
      ,[Tab_name]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Report_type]
      ,[Excel_type]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Option_text]
      ,[Active] FROM [FSoftNAV2WEB].[dbo].Object_properties where Object_id = 1137 and ID = '17539'
  
  Update [FSoftNAV2WEB].[dbo].Object_properties set Option_text = Null where Object_id = '1159' and ID ='17639' 
  Update [FSoftNAV2WEB].[dbo].Object_properties set Option_text = Null where Object_id = '1159' and ID ='17640' 
  Update [FSoftNAV2WEB].[dbo].Object_properties set Option_text = Null where Object_id = '1159' and ID ='17641' 
  
  Select * FROM [FSoftNAV2WEB].[dbo].Object_properties where Object_id = '1159'

insert into [FSoftNAV2WEB].[dbo].Object_properties (
		[Name]
	  ,[Caption]
      ,[Type]
      ,[Data_length]
      ,[Database_id]
      ,[Object_id]
      ,[Tab_name]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Report_type]
      ,[Excel_type]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Option_text]
      ,[Active])  Select [Name],[Caption]
      ,[Type]
      ,[Data_length]
      ,[Database_id]
      --,[Object_id]
      ,'1100'
      ,[Tab_name]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Report_type]
      ,[Excel_type]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Option_text]
      ,[Active] FROM [FSoftNAV2WEB].[dbo].Object_properties Where Object_id = '999'
      
      --drop table menuofgroup use
use FSoftNAV2WEB_TEST
Go
Drop TABLE [FSoftNAV2WEB_TEST].[dbo].[MenuOfUser] 
Go 
drop TABLE [FSoftNAV2WEB_TEST].[dbo].[MenuOfGroupUser] 
Go

Drop table [FSoftNAV2WEB_TEST].[dbo].Menu

Go
USE [FSoftNAV2WEB_TEST]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MenuOfUser](
	[MenuID] [int] NOT NULL,
	[UserID] [int] NOT NULL,
 CONSTRAINT [PK_MenuOfUser] PRIMARY KEY CLUSTERED 
(
	[MenuID] ASC,
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

USE [FSoftNAV2WEB_TEST]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Menu](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Caption] [nvarchar](50) NOT NULL,
	[Url] [varchar](500) NULL,
	[UrlCard] [varchar](500) NULL,
	[TableName] [varchar](50) NULL,
	[ObjCardName] [varchar](50) NULL,
	[ObjListName] [varchar](50) NULL,
	[LineTableName] [varchar](50) NULL,
	[LineObjName] [varchar](50) NULL,
	[LineField] [varchar](50) NULL,
	[PageType] [int] NULL,
	[ParentID] [int] NULL,
	[Level] [int] NULL,
	[Icon] [varchar](50) NULL,
	[IconUrl] [varchar](100) NULL,
	[CssClass] [varchar](50) NULL,
	[Active] [bit] NULL,
	[SortIndex] [int] NULL,
 CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [FSoftNAV2WEB_TEST]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MenuOfGroupUser](
	[UserGroupID] [int] NOT NULL,
	[MenuID] [int] NOT NULL,
 CONSTRAINT [PK_MenuOfGroupUser] PRIMARY KEY CLUSTERED 
(
	[UserGroupID] ASC,
	[MenuID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MenuOfGroupUser]  WITH CHECK ADD  CONSTRAINT [FK_MenuOfGroupUser_Menu] FOREIGN KEY([MenuID])
REFERENCES [dbo].[Menu] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MenuOfGroupUser] CHECK CONSTRAINT [FK_MenuOfGroupUser_Menu]
GO

ALTER TABLE [dbo].[MenuOfGroupUser]  WITH CHECK ADD  CONSTRAINT [FK_MenuOfGroupUser_UserGroup] FOREIGN KEY([UserGroupID])
REFERENCES [dbo].[UserGroup] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[MenuOfGroupUser] CHECK CONSTRAINT [FK_MenuOfGroupUser_UserGroup]
GO

Go

insert into [FSoftNAV2WEB_TEST].[dbo].Object_properties (
		[Name]
	  ,[Caption]
      ,[Type]
      ,[Data_length]
      ,[Database_id]
      ,[Object_id]
      ,[Tab_name]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Report_type]
      ,[Excel_type]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Option_text]
      ,[Active])  Select [Name],[Caption]
      ,[Type]
      ,[Data_length]
      ,[Database_id]
      ,[Object_id]
      --,'1100'
      ,[Tab_name]
      ,[Show_in_form]
      ,[Show_in_grid]
      ,[Show_in_list]
      ,[Show_in_lookup]
      ,[Show_in_report]
      ,[Show_in_excel]
      ,[Form_tab_index]
      ,[Grid_order]
      ,[Report_type]
      ,[Excel_type]
      ,[Editable]
      ,[Init_value]
      ,[Date_formula]
      ,[Auto_format_expr]
      ,[Auto_format_type]
      ,[Not_blank]
      ,[Caption_ML]
      ,[Tool_tip_ML]
      ,[Min_value]
      ,[Max_value]
      ,[Control_addIn]
      ,[Look_up_page]
      ,[Decimal_places]
      ,[Char_allowed]
      ,[Values_allowed]
      ,[Extended_info]
      ,[Option_text]
      ,[Active] FROM [FSoftNAV2WEB].[dbo].Object_properties
Go

INSERT INTO [FSoftNAV2WEB_TEST].[dbo].[MenuOfGroupUser]
           ([UserGroupID]
           ,[MenuID])    
SELECT [UserGroupID]
      ,[MenuID]
  FROM [FSoftNAV2WEB].[dbo].[MenuOfGroupUser]
  
  
INSERT INTO [FSoftNAV2WEB_TEST].[dbo].[MenuOfUser]
           (MenuID
           ,[UserID])    
SELECT MenuID
      ,[UserID]
  FROM [FSoftNAV2WEB].[dbo].[MenuOfUser]

insert into [FSoftNAV2WEB].[dbo].Object (
		[Name]
      ,[Type]
      ,[Active]
      ,[Database_id]
      )  Select 'VMI Line NPP'
      ,[Type]
      ,[Active]
      ,[Database_id] FROM [FSoftNAV2WEB].[dbo].Object where id = '1173'

UPDATE [FSoftNAV2WEB_DMS].[dbo].[MenuOfUser]
   SET [View] = '1'
      ,[New] = '1'
      ,[Edit] = '1'
      ,[Delete] = '1'
      ,[LView] = '1'
      ,[LNew] = '1'
      ,[LEdit] = '1'
      ,[LDelete] = '1'
 WHERE userid = '25'

*/ 




